﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
using System.Data;

namespace CrmApplication.Models
{
	public class ContactModels
	{
		#region RegionMappingContact

		public decimal IdContact{ get; set; }
		public string FirstName { get; set; }
		public string LastName { get; set; }
		public string Sex { get; set; }

		public decimal IdPosition { get; set; }
		public string IdPosition_val { get; set; }
		public string PositionName { get; set; }

		public decimal IdSubPosition { get; set; }
		public string IdSubPosition_val { get; set; }
		public string SubPositionName { get; set; }

		public decimal IdCompany { get; set; }
		public string IdCompany_val { get; set; }
		public string CompanyName { get; set; }

		public decimal IdBranch { get; set; }
		public string IdBranch_val { get; set; }
		public string BranchName { get; set; }

		public decimal IdCity { get; set; }
		public string IdCity_val { get; set; }
		public string CityName { get; set; }

		public decimal IdCounty { get; set; }

		public decimal IdCampaign { get; set; }
		public string CampaignName { get; set; }

		public decimal IdTStatus { get; set; }
		public string TStatusName { get; set; }

		public string Address { get; set; }
		public string AddressDetail { get; set; }

		public string Email1 { get; set; }
		public string Email2 { get; set; }

		public string PhoneNumber { get; set; }
		public string MobilNumber { get; set; }

		public string Available { get; set; }

		public decimal IdIncome { get; set; }
		public string IdIncome_val { get; set; }

		public decimal IdDivision { get; set; }
		public string IdDivision_val { get; set; }

		public decimal IdAge { get; set; }
		public string IdAge_val { get; set; }

		public decimal IdPriority { get; set; }
		public string IdPriority_val { get; set; }

		public decimal IdDomain { get; set; }
		public string IdDomain_val { get; set; }

		public decimal IdSubdomain { get; set; }
		public string IdSubdomain_val { get; set; }

		public decimal IdEmployee { get; set; }
		public string IdEmployee_val { get; set; }

		public decimal IdTurnover { get; set; }
		public string IdTurnover_val { get; set; }

		public string WebAddress { get; set; }

		public DateTime BirthDate { get; set; }

		public decimal CreatedBy { get; set; }
		public DateTime DateCreated { get; set; }

		public string Observations { get; set; }

		#endregion


		
		#region RegionOracleConnect

		private static OracleConnection connect;

		static ContactModels ()
		{
			string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
			connect = new OracleConnection(connString);
		}

		#endregion

		#region RegionDatabaseRecords
		/// <summary>
		/// select all persons from database
		/// </summary>
		/// <param name="contact"></param>
		/// <returns></returns>
		public static List<ContactModels> GetContacts (ContactModels contact)
		{
			string fName = "";
			if ((contact.FirstName != "")&(contact.FirstName!=null))
			{
				fName = " and lower(p.first_name) like '%" + contact.FirstName + "%' ";
			}			

			string lName = "";
			if ((contact.LastName != null)&(contact.LastName!=""))
			{
				lName = " and lower(p.last_name) like '%" + contact.LastName + "%' ";
			}

			string age = "";
			if (contact.IdAge != 0)
			{
				age = " join nomage na "
					   + "on p.id_age=na.id_age and na.id_age= " + contact.IdAge ;
			}

			string position = "";
			if (contact.IdPosition != 0)
			{
				position = " and p.id_position= " + contact.IdPosition ;
			}

			string subposition = "";
			if (contact.IdSubPosition != 0)
			{
				subposition = " and p.id_subposition= " + contact.IdSubPosition;
			}

			string division = "";
			if (contact.IdDivision != 0)
			{
				division = "join division d on p.id_division=d.id_division "
						   + " and p.id_division= " + contact.IdDivision ;
			}

			string priority = "";
			if (contact.IdPriority != 0)
			{
				priority = " join nompriority np "
						   + "on p.id_priority=np.id_priority and np.id_priority= " + contact.IdPriority ;
			}

			string sex = "";
			if ((contact.Sex != null)&(contact.Sex!=""))
			{
				sex = " and p.sex='" +contact.Sex+ "' ";	
			}

			string available = "";
			if ((contact.Available != "")&(contact.Available!=null))
			{				
				available = " and p.available='" + contact.Available +"' ";				
					
			}

			string company = "";
			if (contact.IdCompany != 0)
			{
				company = " and c.id_company= " + contact.IdCompany;
			}

			string domain = "";
			if (contact.IdDomain != 0)
			{
				domain = " join domains d on p.id_domain=d.id_domain "
							+ "and d.id_domain= " + contact.IdDomain;
			}

			string subdomain = "";
			if (contact.IdSubdomain != 0)
			{
				subdomain = " join subdomain sd on p.id_subdomain=sd.id_subdomain "
							+ "and sd.id_subdomain= " + contact.IdSubdomain ;
			}

			string city = "";
			if (contact.IdCity != 0)
			{
				city = " and nc.id_city= " + contact.IdCity ;
			}

			string county = "";
			if (contact.IdCounty != 0)
			{
				county = "  join nomcounty nct on nc.id_county=nct.id_county "
							+ " and nct.id_county= " + contact.IdCounty ;
			}

			string employees = "";
			if (contact.IdEmployee != 0)
			{
				employees = " join nomemployee ne on c.id_employee=ne.id_employee "
							+ " and c.id_employee= " + contact.IdEmployee ;
			}

			string income = "";
			if (contact.IdIncome != 0)
			{
				income = " join nomincome ni on p.id_income=ni.id_income "
							+ " and ni.id_income= " + contact.IdIncome;
			}

			string turover = "";
			if (contact.IdTurnover != 0)
			{
				turover = " join nomturnover nt on c.id_turnover=nt.id_turnover "
							+ " and c.id_turnover= " + contact.IdTurnover ;
			}

			string selectString = "	select p.id_person,p.last_name,p.first_name,p.sex,np.position_name, "
									+ " nsp.subposition_name,c.company_name,b.address,nc.city_name "
									+ " from person p "
									+ " join position np on p.id_position=np.id_position "
									+ fName
									+ lName
									+ sex
									+ available
									+ position
									+ " join subposition nsp on p.id_subposition = nsp.id_subposition "
									+ subposition
									+ " join company c on p.id_company = c.id_company "
									+ company
									+ " join branch b on p.id_branch = b.id_branch "
									+ " join nomcity nc on b.id_city = nc.id_city "
									+ city
									+ county
									+ age
									+ division
									+ priority
									+ employees
									+ turover
									+ domain
									+ subdomain
									+ income
									+ " order by p.last_name,p.first_name ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			List<ContactModels> result = new List<ContactModels>();
			ContactModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new ContactModels();
						result1.IdContact= sdr.GetDecimal(0);
						result1.LastName = sdr.GetString(1);
						result1.FirstName = sdr.GetString(2);
						result1.Sex = sdr.GetString(3);
						result1.PositionName = sdr.GetString(4);
						result1.SubPositionName = sdr.GetString(5);
						result1.CompanyName = sdr.GetString(6);
						result1.Address = sdr.GetString(7);
						//result1.CityName = sdr.GetString(8);
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// insert new person
		/// </summary>
		/// <param name="Contact"></param>
		public static void AddContact (ContactModels Contact)
		{
			string insert = "INSERT INTO Person(last_name,first_name,sex,email1,email2,mobil_number,phone_number,web_address,id_position,id_subposition,id_division,id_age,id_company,id_priority,id_domain,id_subdomain,available,id_income,observations,id_branch) "
							+ " VALUES (:lastName,:firtsName,:sex,:email1,:email2,:mobilNumber,:phoneNumber,:web,:idPosition,:idSubposition,:idDivision,:idAge,:idCompany,:idPriority,:idDomain,:idSubdomain,:available,:idIncome,:obs,:idBranch)";

			OracleCommand Contactcomm = new OracleCommand(insert);
			Contactcomm.Parameters.Add("lastName", Contact.LastName);
			Contactcomm.Parameters.Add("firstName", Contact.FirstName);
			Contactcomm.Parameters.Add("sex", Contact.Sex);
			Contactcomm.Parameters.Add("email1", Contact.Email1);
			Contactcomm.Parameters.Add("email2", Contact.Email2);
			Contactcomm.Parameters.Add("mobilNumber", Contact.MobilNumber);
			Contactcomm.Parameters.Add("phoneNumber", Contact.PhoneNumber);
			Contactcomm.Parameters.Add("web", Contact.WebAddress);
			Contactcomm.Parameters.Add("idPosition", Contact.IdPosition);
			Contactcomm.Parameters.Add("idSubposition", Contact.IdSubPosition);
			Contactcomm.Parameters.Add("idDivision", Contact.IdDivision);
			Contactcomm.Parameters.Add("idAge", Contact.IdAge);
			Contactcomm.Parameters.Add("idCompany", Contact.IdCompany);
			Contactcomm.Parameters.Add("idPriority", Contact.IdPriority);
			Contactcomm.Parameters.Add("idDomain", Contact.IdDomain);
			Contactcomm.Parameters.Add("idSubdomain", Contact.IdSubdomain);
			Contactcomm.Parameters.Add("available", Contact.Available);
			Contactcomm.Parameters.Add("idIncome", Contact.IdIncome);
			Contactcomm.Parameters.Add("obs", Contact.Observations);
			Contactcomm.Parameters.Add("idBranch", Contact.IdBranch);
			/*				
			Contactcomm.Parameters.Add("birthDate", Contact.BirthDate);
			Contactcomm.Parameters.Add("createdBy", Contact.CreatedBy);
			Contactcomm.Parameters.Add("dateCreated", Contact.DateCreated);*/

			Contactcomm.Connection = connect;

			try
			{
				connect.Open();
				Contactcomm.ExecuteNonQuery();
			}
			finally
			{
				connect.Close();
			}
		}

		/// <summary>
		/// select records for selected id
		/// </summary>
		/// <param name="idContact"></param>
		/// <returns></returns>
		public static List<ContactModels> GetContactById (decimal idContact)
		{
			string selectString = "select p.last_name,p.first_name,p.sex,p.id_age,na.age_name,"
									+ " p.id_income,ni.income_name,p.email1,p.email2,p.web_address, "
									+ " p.id_position, pp.position_name,p.id_subposition,sps.subposition_name, "
									+ " p.id_division,d.division_name,p.id_priority,np.priority_name,p.observations, "
									+ " p.id_company,c.company_name,p.id_branch,b.branch_name, "
									+ " p.id_domain,nd.domain_name,p.id_subdomain,nsd.subdomain_name, " 
									+ " p.phone_number,p.mobil_number,p.available "
									+ " from person p join nomage na on p.id_age = na.id_age "
									+ " join nomincome ni on p.id_income = ni.id_income "
									+ " join position pp on p.id_position = pp.id_position " 
									+ " join subposition sps on p.id_subposition = sps.id_subposition " 
									+ " join division d on p.id_division = d.id_division "
									+ " join nompriority np on p.id_priority = np.id_priority "
									+ " join company c on p.id_company = c.id_company "
									+ " join branch b on p.id_branch = b.id_branch "
									+ " join domains nd on p.id_domain = nd.id_domain "
									+ " join subdomain nsd on p.id_subdomain = nsd.id_subdomain "
									+ " where p.id_person = :idContact ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":id", idContact);

			List<ContactModels> result = new List<ContactModels>();
			ContactModels result1 = null;

			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{						
							result1 = new ContactModels();
							result1.LastName = sdr.GetString(0);
							result1.FirstName = sdr.GetString(1);
							result1.Sex = sdr.GetString(2);
							result1.IdAge = sdr.GetDecimal(3);
							result1.IdAge_val = sdr.GetString(4);
							result1.IdIncome = sdr.GetDecimal(5);
							result1.IdIncome_val = sdr.GetString(6);
							result1.Email1 = sdr.GetString(7);
							if(!sdr.IsDBNull(8))
							{
								result1.Email2 = sdr.GetString(8);
							}
							if (!sdr.IsDBNull(9))
							{
								result1.WebAddress = sdr.GetString(9);
							}
							
							result1.IdPosition = sdr.GetDecimal(10);
							result1.IdPosition_val = sdr.GetString(11);
							result1.IdSubPosition = sdr.GetDecimal(12);
							result1.IdSubPosition_val = sdr.GetString(13);
							result1.IdDivision = sdr.GetDecimal(14);
							result1.IdDivision_val = sdr.GetString(15);
							result1.IdPriority = sdr.GetDecimal(16);
							result1.IdPriority_val = sdr.GetString(17);
							if (!sdr.IsDBNull(18))
							{
								result1.Observations = sdr.GetString(18);
							}
							
							result1.IdCompany = sdr.GetDecimal(19);
							result1.IdCompany_val = sdr.GetString(20);
							result1.IdBranch = sdr.GetDecimal(21);
							result1.IdBranch_val = sdr.GetString(22);
							result1.IdDomain = sdr.GetDecimal(23);
							result1.IdDomain_val = sdr.GetString(24);
							result1.IdSubdomain = sdr.GetDecimal(25);
							if (!sdr.IsDBNull(26))
							{
								result1.IdSubdomain_val = sdr.GetString(26);
							}
							if (!sdr.IsDBNull(27))
							{
								result1.PhoneNumber = sdr.GetString(27);
							}
							if (!sdr.IsDBNull(28))
							{
								result1.MobilNumber = sdr.GetString(28);
							}
							
							result1.Available = sdr.GetString(29);
							//result1.IdContact = sdr.GetDecimal(30);

							result.Add(result1);						
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// select all contacts asigned to a selected campaign
		/// </summary>
		/// <param name="contact"></param>
		/// <returns></returns>
		public static List<ContactModels> GetContactsByCampaign (ContactModels contact,string status,string stype)
		{
			string tStatus = "";
			if ((status != "") & (status != null))
			{
				if ((stype != "") & (stype != null))
				{
					tStatus = " and cp.id_tstatus "+ stype + "(" + status + ")";
				}
			}

			string fName = "";
			if ((contact.FirstName != "") & (contact.FirstName != null))
			{
				fName = " and lower(p.first_name) like '%" + contact.FirstName + "%' ";
			}

			string lName = "";
			if ((contact.LastName != null) & (contact.LastName != ""))
			{
				lName = " and lower(p.last_name) like '%" + contact.LastName + "%' ";
			}
			
			string position = "";
			if (contact.IdPosition != 0)
			{
				position =  " join position pos on p.id_position=pos.id_position "
							+ " and p.id_position= " + contact.IdPosition;
			}

			string subposition = "";
			if (contact.IdSubPosition != 0)
			{
				subposition =	" join subposition sup on p.id_subposition = sup.id_subposition "
								+ " and p.id_subposition= " + contact.IdSubPosition;
			}

			string city = "";
			if (contact.IdCity != 0)
			{
				city = " join nomcity nc on b.id_city=nc.id_city "
						+ " and nc.id_city= " + contact.IdCity;
			}

			string county = "";
			if (contact.IdCounty != 0)
			{
				county = "  join nomcounty nct on b.id_county=nct.id_county "
							+ " and nct.id_county= " + contact.IdCounty;
			}

			string company = "";
			if (contact.IdCompany != 0)
			{
				company = " and p.id_company= " + contact.IdCompany;
			}

			string selectString = "	select cp.id_person,cp.id_campaign,cp.id_tstatus,"
									+ " p.last_name,p.first_name,p.phone_number,"
									+ " p.mobil_number,c.company_name,ts.tstatus_name "
									+ " from campaignperson cp "
									+ " join person p on cp.id_person=p.id_person "
									+ tStatus
									+ " join telestatus ts on cp.id_tstatus = ts.id_tstatus "
									+ fName
									+ lName
									+ " join company c on p.id_company=c.id_company "
									+ company
									+ " join branch b on c.id_company=b.id_company "
									+ position
									+ subposition 
									+ city 
									+ county
									+ " where cp.id_campaign=:idCampaign "									
									+ " order by p.last_name,p.first_name ";

			OracleCommand loginComm = new OracleCommand(selectString);
			loginComm.Connection = connect;

			loginComm.Parameters.Add(":idCampaign", contact.IdCampaign);

			List<ContactModels> result = new List<ContactModels>();
			ContactModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = loginComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new ContactModels();
						result1.IdContact = sdr.GetDecimal(0);
						result1.IdCampaign = sdr.GetDecimal(1);
						result1.IdTStatus = sdr.GetDecimal(2);
						if (!sdr.IsDBNull(3))
						{
							result1.LastName = sdr.GetString(3);
						}
						if (!sdr.IsDBNull(4))
						{
							result1.FirstName = sdr.GetString(4);
						}						
						if (!sdr.IsDBNull(5))
						{
							result1.PhoneNumber = sdr.GetString(5);
						}	
						if (!sdr.IsDBNull(6))
						{
							result1.MobilNumber = sdr.GetString(6);
						}
						if (!sdr.IsDBNull(7))
						{
							result1.CompanyName = sdr.GetString(7);
						}
						if (!sdr.IsDBNull(8))
						{
							result1.TStatusName = sdr.GetString(8);
						}
						result.Add(result1);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result;
		}

		/// <summary>
		/// update contact
		/// </summary>
		/// <param name="contact"></param>
		/// <returns></returns>
		public static ContactModels EditContact (ContactModels contact)
		{
			string selectString = "update person set "
									+ " last_name=:lastName,first_name=:firstName,sex=:sex "									
									+ " id_age=:idAge,id_income=:idIncome,email1=:email1,email2=:email2, "
									+ " web_address=:web,id_position=:idPosition,id_subposition=:idSubposition, "
									+ " id_division=:idDivision,id_priority=:idPriority,observations=:obs, "
									+ " id_company=:idCompany,id_branch=:idBranch,id_domain=:idDomain, "
									+ " id_subdomain=:idSubdomain,phone_number=:phoneNr,mobil_number=:mobilNr,available=:available "									
									+ " where id_person=:idPerson ";  
									

			OracleCommand updateComm = new OracleCommand(selectString);
			updateComm.Connection = connect;
			updateComm.Parameters.Add(":lastName", contact.LastName);
			updateComm.Parameters.Add(":firstName", contact.FirstName);
			updateComm.Parameters.Add(":sex", contact.Sex);
			updateComm.Parameters.Add(":idPerson", contact.IdContact); ;
			/*updateComm.Parameters.Add(":idAge", contact.IdAge);
			updateComm.Parameters.Add(":idIncome", contact.IdIncome);
			updateComm.Parameters.Add(":email1", contact.Email1);
			updateComm.Parameters.Add(":email2", contact.Email2);
			updateComm.Parameters.Add(":web", contact.WebAddress);
			updateComm.Parameters.Add(":idPosition", contact.IdPosition);
			updateComm.Parameters.Add(":idSubposition", contact.IdSubPosition);
			updateComm.Parameters.Add(":idDivision", contact.IdDivision);
			updateComm.Parameters.Add(":idPriority", contact.IdPriority);
			updateComm.Parameters.Add(":obs", contact.Observations);
			updateComm.Parameters.Add(":idCompany", contact.IdCompany);
			updateComm.Parameters.Add(":idBranch", contact.IdBranch);
			updateComm.Parameters.Add(":idDomain", contact.IdDomain);
			updateComm.Parameters.Add(":idSubdomain", contact.IdSubdomain);
			updateComm.Parameters.Add(":phoneNr", contact.PhoneNumber);
			updateComm.Parameters.Add(":mobilNr", contact.MobilNumber);
			updateComm.Parameters.Add(":available", contact.Available);*/			

			ContactModels result1 = null;
			try
			{
				connect.Open();
				using (OracleDataReader sdr = updateComm.ExecuteReader())
				{
					while (sdr.Read())
					{
						result1 = new ContactModels();
						result1.LastName = sdr.GetString(0);
						result1.FirstName = sdr.GetString(1);
						result1.Sex = sdr.GetString(2);
						/*result1.IdAge = sdr.GetDecimal(3);						
						result1.IdIncome = sdr.GetDecimal(5);						
						result1.Email1 = sdr.GetString(6);
						result1.Email2 = sdr.GetString(7);
						result1.WebAddress = sdr.GetString(8);
						result1.IdPosition = sdr.GetDecimal(9);						
						result1.IdSubPosition = sdr.GetDecimal(10);						
						result1.IdDivision = sdr.GetDecimal(11);						
						result1.IdPriority = sdr.GetDecimal(12);						
						result1.Observations = sdr.GetString(13);
						result1.IdCompany = sdr.GetDecimal(14);						
						result1.IdBranch = sdr.GetDecimal(15);						
						result1.IdDomain = sdr.GetDecimal(16);						
						result1.IdSubdomain = sdr.GetDecimal(17);						
						result1.PhoneNumber = sdr.GetString(18);
						result1.MobilNumber = sdr.GetString(19);
						result1.Available = sdr.GetString(20);*/
						result1.IdContact = sdr.GetDecimal(3);
					}
				}
			}
			finally
			{
				connect.Close();
			}
			return result1;
		}
		
		/* get contacts using packages and stored procedure
		 public static List<ContactModels> GetContacts()
		{
          
		  OracleCommand loginComm = new OracleCommand();
		  loginComm.Connection = connect;
		  loginComm.CommandText = "CONTACTS.GETCONTACTS";
		  loginComm.CommandType = CommandType.StoredProcedure;
		  loginComm.Parameters.Add("data_cursor",OracleDbType.RefCursor).Direction = ParameterDirection.Output; ;

		  List<ContactModels> result = new List<ContactModels>();
		  ContactModels result1 = null;
		  try
		  {
			connect.Open();
			using (OracleDataReader sdr = loginComm.ExecuteReader())
			{
			  while (sdr.Read())
			  {
				result1 = new ContactModels();
				result1.IdPerson = sdr.GetDecimal(0);
				result1.FirstName = sdr.GetString(1);
				result1.LastName = sdr.GetString(2);
				result1.Sex = sdr.GetString(3);
				result1.PositionName = sdr.GetString(4);
				result1.SubPositionName = sdr.GetString(5);
				result1.CompanyName = sdr.GetString(6);
				result1.Address = sdr.GetString(7);
				result1.CityName = sdr.GetString(8);
				result.Add(result1);
			  }
			}
		  }
		  finally
		  {
			connect.Close();
		  }
		  return result;
		}*/
		#endregion


		#region RegionJsonArrays
		/// <summary>
		/// create json array for all contacts
		/// </summary>
		/// <param name="contact"></param>
		/// <returns></returns>
		public static ContactContainer GetContactArray (ContactModels contact)
		{
			ContactModels[] data = new ContactModels[20];
			data = ContactModels.GetContacts(contact).ToArray();

			ContactContainer container = new ContactContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array by id
		/// </summary>
		/// <param name="idContact"></param>
		/// <returns></returns>
		public static ContactContainer GetContactArrayById (decimal idContact)
		{
			ContactModels[] data = new ContactModels[20];
			data = ContactModels.GetContactById(idContact).ToArray();

			ContactContainer container = new ContactContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}

		/// <summary>
		/// create json array for selecting contacts by campaign
		/// </summary>
		/// <param name="contact"></param>
		/// <returns></returns>
		public static ContactContainer GetContactArrayByIdCampaign (ContactModels contact,string status,string stype)
		{
			ContactModels[] data = new ContactModels[20];
			data = ContactModels.GetContactsByCampaign(contact,status,stype).ToArray();

			ContactContainer container = new ContactContainer()
			{
				Records = data,
				Count = data.Length
			};

			return container;
		}
		#endregion
	}

	public class ContactContainer
	{
		public ContactModels[] Records { get; set; }
		public int Count { get; set; }
	}
}